﻿
Imports System.Data.OracleClient
Imports ccapps.Web.page

Namespace persistent

    Public Class CustomerFinancialDetails

        Private Shared ConnStr As String = GlobalVar.OraConnStr

        Private Shared dbPackageName As String = "eu_bill.FinancialDetails."
        

       

        Public Shared Function FinancialDocCustInfoMSISDN(ByVal pin_msisdn As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "FinancialDocCustInfoMSISDN")
            command.Parameters.Add("pin_msisdn", OracleType.VarChar).Value = pin_msisdn
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            'LOGLAMA
            Dim Query As String = "Select * from dual "
            Dim gen As Object = New GPOPrepaidToAveaPostpaidCustomerCreate()
            persistent.myDBConn.ORAScalar_LOG(Query, "", "", "null", pin_msisdn, "Select(FinancialDocCustInfoMSISDN)", "FinancialDocuments", gen.Session("user"))
            Return ExecuteDataTable(command)

        End Function
        'PROCEDURE DisplayItems (pin_csid IN integer,p_cursor OUT r_cursor_type)
        Public Shared Function DisplayItems(ByVal pin_docrefnum As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "DisplayItems")
            command.Parameters.Add("pin_docrefnum", OracleType.VarChar).Value = pin_docrefnum
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            'LOGLAMA
            'Dim Query As String = "Select * from dual "
            'Dim gen As Object = New GPOPrepaidToAveaPostpaidCustomerCreate()
            'persistent.myDBConn.ORAScalar_LOG(Query, "", pin_csid, "null", "", "Select(DisplayItems)", "FinancialDocuments", gen.Session("user"))
            Return ExecuteDataTable(command)

        End Function
        'PROCEDURE DisplayTax (pin_csid IN integer,p_cursor OUT r_cursor_type)
        'PROCEDURE DisplayItems (pin_docrefnum IN varchar2,p_cursor OUT r_cursor_type)
        Public Shared Function DisplayTax(ByVal pin_docrefnum As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "DisplayTax")
            command.Parameters.Add("pin_docrefnum", OracleType.VarChar).Value = pin_docrefnum
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            'LOGLAMA
            'Dim Query As String = "Select * from dual "
            'Dim gen As Object = New GPOPrepaidToAveaPostpaidCustomerCreate()
            'persistent.myDBConn.ORAScalar_LOG(Query, "", pin_csid, "null", "", "Select(DisplayTax)", "FinancialDocuments", gen.Session("user"))
            Return ExecuteDataTable(command)

        End Function

        Public Shared Function FinancialDocCustInfoCustcode(ByVal pin_custcode As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "FinancialDocCustInfoCustcode")
            command.Parameters.Add("pin_custcode", OracleType.VarChar).Value = pin_custcode
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            'LOGLAMA
            Dim Query As String = "Select * from dual "
            Dim gen As Object = New GPOPrepaidToAveaPostpaidCustomerCreate()
            persistent.myDBConn.ORAScalar_LOG(Query, pin_custcode, "", "null", "", "Select(FinancialDocCustInfoCustcode)", "FinancialDocuments", gen.Session("user"))
            Return ExecuteDataTable(command)

        End Function

        Public Shared Function FinancialDocCustInfo(ByVal pin_customer_id As Integer, ByVal pin_valid_from As String, ByVal pin_valid_until As String, ByVal pin_doc_status As Integer, ByVal pin_status As Integer, ByVal pin_valid_from_status As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "FinancialDocCustInfo")
            command.Parameters.Add("pin_customer_id", OracleType.VarChar).Value = pin_customer_id
            command.Parameters.Add("pin_valid_from", OracleType.VarChar).Value = pin_valid_from
            command.Parameters.Add("pin_valid_until", OracleType.VarChar).Value = pin_valid_until
            command.Parameters.Add("pin_doc_status", OracleType.VarChar).Value = pin_doc_status
            command.Parameters.Add("pin_status", OracleType.Int32).Value = pin_status
            command.Parameters.Add("pin_empty_search", OracleType.Int32).Value = pin_valid_from_status
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            'LOGLAMA
            Dim Query As String = "Select * from dual "
            Dim gen As Object = New GPOPrepaidToAveaPostpaidCustomerCreate()
            persistent.myDBConn.ORAScalar_LOG(Query, "", pin_customer_id, "null", "", "Select(FinancialDocCustInfo)", "FinancialDocuments", gen.Session("user"))
            Return ExecuteDataTable(command)

        End Function

        Public Shared Function FinancialDocCustInfoCOID(ByVal pin_coid As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "FinancialDocCustInfoCOID")
            command.Parameters.Add("pin_coid", OracleType.Int32).Value = pin_COID
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            'LOGLAMA
            Dim Query As String = "Select * from dual "
            Dim gen As Object = New GPOPrepaidToAveaPostpaidCustomerCreate()
            persistent.myDBConn.ORAScalar_LOG(Query, "", "", pin_coid, "", "Select(FinancialDocCustInfoCOID)", "FinancialDocuments", gen.Session("user"))
            Return ExecuteDataTable(command)

        End Function

        Public Shared Function DealerControl(ByVal pin_custcode As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "DealerControl")
            command.Parameters.Add("pin_custcode", OracleType.VarChar).Value = pin_custcode
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            'LOGLAMA
            Dim Query As String = "Select * from dual "
            Dim gen As Object = New GPOPrepaidToAveaPostpaidCustomerCreate()
            persistent.myDBConn.ORAScalar_LOG(Query, pin_custcode, "", "", "", "Select(DealerControl)", "FinancialDocuments", gen.Session("user"))
            Return ExecuteDataTable(command)

        End Function

        Public Shared Function DispalyComplaintReason() As DataTable

            Dim command As New OracleCommand(dbPackageName & "DispalyComplaintReason")
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function
        ' procedure DisplayBillingAccountid (pin_customer_id IN integer,p_cursor OUT r_cursor_type) 
        Public Shared Function DisplayBillingAccountid(pin_customer_id As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "DisplayBillingAccountid")
            command.Parameters.Add("pin_customer_id", OracleType.Int32).Value = pin_customer_id
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function

        'PROCEDURE SetComplaint (pin_customer_id IN integer,pin_reason  IN Integer,pin_ref_num IN Varchar2,p_cursor OUT r_cursor_type)
        Public Shared Function SetComplaint(pin_customer_id As Integer, pin_reason As Integer, pin_ref_num As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "SetComplaint")
            command.Parameters.Add("pin_customer_id", OracleType.Int32).Value = pin_customer_id
            command.Parameters.Add("pin_reason", OracleType.Int32).Value = pin_reason
            command.Parameters.Add("pin_ref_num", OracleType.VarChar).Value = pin_ref_num
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            'LOGLAMA
            Dim Query As String = "Select * from dual "
            Dim gen As Object = New GPOPrepaidToAveaPostpaidCustomerCreate()
            persistent.myDBConn.ORAScalar_LOG(Query, "", pin_customer_id, "", "", "Select(SetComplaint)", "FinancialDocuments", gen.Session("user"))

            Return ExecuteDataTable(command)

        End Function

        'PROCEDURE DisplayMoney(p_cursor OUT r_cursor_type)
        Public Shared Function DisplayMoney() As DataTable

            Dim command As New OracleCommand(dbPackageName & "DisplayMoney")
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function
        'PROCEDURE SetCreditInvoice (pin_amount IN Float,pin_remark IN Varchar2,pin_customer_id IN Integer,pin_currency IN Integer,p_cursor OUT r_cursor_type)
        Public Shared Function SetCreditInvoice(pin_amount As Double, pin_remark As String, pin_customer_id As Integer, pin_currency As Integer, pin_user_info As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "SetCreditInvoice")
            command.Parameters.Add("pin_amount", OracleType.Float).Value = pin_amount
            command.Parameters.Add("pin_remark", OracleType.VarChar).Value = pin_remark
            command.Parameters.Add("pin_customer_id", OracleType.Int32).Value = pin_customer_id
            command.Parameters.Add("pin_currency", OracleType.Int32).Value = pin_currency
            command.Parameters.Add("pin_user_info", OracleType.VarChar).Value = pin_user_info

            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)
            'LOGLAMA



        End Function
        'PROCEDURE DisplayCustomerDetails (pin_customer_id IN integer,pin_ohrefnum IN integer,p_cursor OUT r_cursor_type)
        Public Shared Function DisplayCustomerDetails(pin_customer_id As Integer, pin_ohrefnum As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "DisplayCustomerDetails")
            command.Parameters.Add("pin_customer_id", OracleType.Int32).Value = pin_customer_id
            command.Parameters.Add("pin_ohrefnum", OracleType.VarChar).Value = pin_ohrefnum
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)
            'LOGLAMA



        End Function


        Public Shared Function ExecuteNonQuery(ByVal command As OracleCommand, ByRef MESSAGE As String)
            Dim connection As New OracleConnection(ConnStr)
            command.Connection = connection
            command.CommandType = CommandType.StoredProcedure

            Try
                connection.Open()
                command.ExecuteNonQuery()
            Catch ex As Exception
                MESSAGE = ex.Message.ToString
            Finally
                connection.Close()
            End Try
        End Function
        Public Shared Function ExecuteDataTable(ByVal command As OracleCommand, Optional ByVal directSql As Boolean = 0) As DataTable
            Dim connection As New OracleConnection(ConnStr)
            Dim dataAdapter As New OracleDataAdapter(command)
            Dim dataTable As New DataTable
            Dim dataSet As New DataSet

            command.Connection = connection

            If Not (directSql) Then
                command.CommandType = CommandType.StoredProcedure
            End If

            Try
                connection.Open()
                dataAdapter.Fill(dataSet)
                dataTable = dataSet.Tables(0)
            Catch ex As Exception
            Finally
                connection.Close()
                dataSet.Dispose()
            End Try

            Return dataTable
        End Function




    End Class
End Namespace